package nutella89.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class J23_MemberDAO implements J12_MemberService {

	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "hr";
	private static final String PWD = "hr";

	private static final String insertSQL = " insert into member(userid, jumin, pwd, zip, addr, tel, mp, pay)"
			+ " values(?,?,?,?,?,?,?,?)";
	private static final String selectSQL = " select userid, jumin, mp from member order by userid ";
	private static final String selectOneSQL = " select * from member where userid=?";
	private static final String updateSQL = "update memeber set jumin=?, pwd=?, zip=?, addr=?, tel=?, mp=?, pay=? ";
	private static final String deleteSQL = " delete from member where userid=? ";

	@Override
	public int insertMember(J23_Member m) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt =0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1, m.getUserid());
			ps.setString(2, m.getJumin());
			ps.setString(3, m.getPwd());
			ps.setString(4, m.getZip());
			ps.setString(5, m.getAddr());
			ps.setString(6, m.getTel());
			ps.setString(7, m.getMp());
			ps.setString(8, m.getPay());
			cnt= ps.executeUpdate();
			
		}catch(Exception ex) {
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
			
		return cnt;
		
	}

	@Override
	public int updateMember(J23_Member m) {
		Connection conn =null;
		PreparedStatement ps =null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps =conn.prepareStatement(updateSQL);
			ps.setString(1, m.getUserid());
			ps.setString(2, m.getJumin());
			ps.setString(3, m.getPwd());
			ps.setString(4, m.getZip());
			ps.setString(5, m.getAddr());
			ps.setString(6, m.getTel());
			ps.setString(7, m.getMp());
			ps.setString(8, m.getPay());
			cnt= ps.executeUpdate();
		} catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
			
		}
		
		return cnt;
	}

	@Override
	public int deleteMember(String m) {
		Connection conn =null;
		PreparedStatement ps =null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps =conn.prepareStatement(deleteSQL);
			ps.setString(1, m);
			cnt =ps.executeUpdate();
			
		} catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
			
		}
		
		return cnt;
	}

	@Override
	public ArrayList<J23_Member> readMember() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<J23_Member> data = new ArrayList<>();

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();

			while (rs.next()) {
				J23_Member m = new J23_Member(rs.getString(1), rs.getString(2),
						"", "", "", rs.getString(3), "", "");

				// 결과집합 rs에 저장된 모든 데이터 중 레코드 단위 행 으로 하나씩 읽어와서 J23_Member 클래스에
				// 저장하고
				// 그것을 ArrayList data변수에 추가
				data.add(m);

			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}

		}
		return data;
	}

	@Override
	public J23_Member readOneMember(String m) {
		Connection conn = null;
		PreparedStatement ps =null;
		ResultSet rs =null;
		J23_Member data =null;
		try {
			Class.forName(DRV);
			conn =DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectOneSQL);
			ps.setString(1, m);
			rs =ps.executeQuery();
			
			while(rs.next()){
				data= new J23_Member(rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),
						rs.getString(6),rs.getString(7), m, m);
				data.setUserid(rs.getString(1));
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null)
				try{rs.close();}catch(Exception e){}
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
		
		return data;
		}
		
}
